#!/usr/bin/env python
# encoding: utf-8

# 导入:
from sqlalchemy import Table, MetaData, Column, Integer,Float, String, DateTime, ForeignKey, create_engine
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy.orm import mapper
from sqlalchemy.ext.declarative import declarative_base

from datetime import datetime
import os

# 创建对象的基类:
Base = declarative_base()

class Data(Base):
    __tablename__ = 'data'
    id = Column(Integer, primary_key=True)
    x            = Column(Float)
    y            = Column(Float)
    z            = Column(Float)
    tm           = Column(Float)
    ts           = Column(Float)
    condition_id = Column(Integer,ForeignKey('condition.id', ondelete='CASCADE'))
    def __init__(self, x=0,y=0,z=0,tm=0,ts=0):
        self.x  = x
        self.y  = y
        self.z  = z
        self.tm = tm
        self.ts = ts

class Condition(Base):
    __tablename__ = 'condition'
    id        = Column(Integer, primary_key=True)
    time      = Column(String(30))
    rayType   = Column(String(30))
    type      = Column(String(20))
    ssd       = Column(String(20))
    power     = Column(String(20))
    direction = Column(String(30))
    deepth    = Column(String(20))
    raySizeX  = Column(String(20))
    raySizeY  = Column(String(20))
    device_id = Column(Integer,ForeignKey('device.id', ondelete='CASCADE'))
    datas     = relationship(Data,
                                backref="condition",
                                cascade="all,delete",
                                passive_deletes=True)
    def __init__(self,
                rayType   = '',
                type      = '',
                ssd       = '',
                power     = '',
                direction = '',
                deepth    = '',
                raySizeX  = '',
                raySizeY  = '',
                datas     = []):
        self.time      = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        self.rayType   = rayType
        self.type      = type
        self.ssd       = ssd
        self.power     = power
        self.direction = direction
        self.deepth    = deepth
        self.raySizeX  = raySizeX
        self.raySizeY  = raySizeY
        self.datas     = datas

class Device(Base):
    '''加速器'''
    __tablename__ = 'device'
    id       = Column(Integer, primary_key=True)
    name     = Column(String(100))
    room     = Column(String(100))
    time     = Column(String(30))
    remark   = Column(String(200))
    conditions = relationship(Condition,
                              backref="device",
                              cascade="all,delete",
                              passive_deletes=True)
    def __init__(self, name='',room='',remark='',conditions=[]):
        self.name     = name
        self.room     = room
        self.remark   = remark
        self.conditions = conditions
        self.time       = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

db_init_flag = False
sqlite_db_path = './watertank.db'
# 初始化数据库连接:
engine = create_engine('sqlite:///%s'%sqlite_db_path)
# 创建数据库
if not os.path.exists(sqlite_db_path):
    Base.metadata.create_all(engine)
    db_init_flag = True

DBSession = sessionmaker(bind=engine)
# 创建session对象:
session = DBSession()

# 添加测试数据
if db_init_flag:
    cond1 = Condition()
    cond1.rayType = '光子'
    data1= Data(x=0,y=0,z=0,tm=4,ts=1)
    data2= Data(x=0,y=0,z=0,tm=4,ts=2)
    data3= Data(x=0,y=0,z=0,tm=4,ts=3)
    data4= Data(x=0,y=0,z=0,tm=4,ts=4)
    cond1.datas.append(data1)
    cond1.datas.append(data2)
    cond1.datas.append(data3)
    cond1.datas.append(data4)
    dev = Device(name='2号加速器',conditions = [cond1])
    session.add(dev)
    # dev = session.query(Device.name).filter(Device.conditions.rayType=='光子')\
        # .filter(Device.id==Condition.device_id)\
        # .first()
    # dev = session.query(Device).filter(Device.name=='2号加速器').first()
    # dev.conditions.append(cond1)
    # print(dev.conditions)
    session.commit()

class SqlDeal:
    def get_device(self):
        dev = session.query(Device).all()
        return dev

    def get_device_sql(self):
        sql = '''select d.id,d.name,d.room,d.remark from device d'''
        result = session.execute(sql).fetchall()
        res = [{'id':r[0],
                'name':r[1],
                'room':r[2],
                'remark':r[3],
                } for r in result]
        return res

    def get_device_by_name_sql(self,name):
        sql = '''select d.id,d.name,d.room from device d where d.name='%s' '''%name
        result = session.execute(sql).fetchall()
        res = [{'id':r[0],
                'name':r[1],
                'room':r[2]
                } for r in result]
        return res

    def get_device_by_id_sql(self,_id):
        sql = '''select d.id,d.name,d.room from device d where d.id='%s' '''%_id
        result = session.execute(sql).fetchall()
        res = [{'id':r[0],
                'name':r[1],
                'room':r[2]
                } for r in result]
        return res

    def insert_device_sql(self,name='',room='',remark=''):
        _time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        sql = '''insert into device(name,room,remark,time)values('%s','%s','%s','%s') '''%(name,room,remark,_time)
        res = session.execute(sql)
        session.commit()
        return res

    def update_device_sql(self,name,room,remark,_id):
        sql = '''update device set name='%s', room='%s', remark='%s' where device.id='%s' '''%(name,room,remark,_id)
        res = session.execute(sql)
        session.commit()
        return res

    def delete_device_sql(self,_id):
        sql = '''delete from device where device.id='%s' '''%_id
        res = session.execute(sql)
        session.commit()
        return res

    def get_condition_sql(self, dev_id):
        sql = '''select c.id,c.rayType,c.type,c.ssd,c.power,c.direction,c.deepth,c.raySizeX,c.raySizeY,c.time from condition c,device d where d.id==c.device_id and d.id==%s '''%dev_id
        result = session.execute(sql).fetchall()
        res = [{'id':        r[0],
                'rayType':   r[1],
                'type':      r[2],
                'ssd':       r[3],
                'power':     r[4],
                'direction': r[5],
                'deepth':    r[6],
                'raySizeX':  r[7],
                'raySizeY':  r[8],
                'time':  r[9],
                } for r in result]
        return res

    def get_condition_data_sql(self, _time):
        sql = '''select c.id,c.rayType,c.type,c.ssd,c.power,c.direction,c.deepth,c.raySizeX,c.raySizeY,c.time,d.name,d.room from condition c, device d where c.time=='%s' and d.id==c.device_id '''%_time
        r = session.execute(sql).fetchall()[0]
        conf = {
                'rayType':   r[1],
                'type':      r[2],
                'ssd':       r[3],
                'power':     r[4],
                'direction': r[5],
                'deepth':    r[6],
                'raySizeX':  r[7],
                'raySizeY':  r[8],
                'time':      r[9],
                'name':      r[10],
                'room':      r[11],
                }
        sql = '''select d.id,d.x,d.y,d.z,d.tm,d.ts from condition c,data d where d.condition_id==c.id and c.time=='%s' '''%_time
        result = session.execute(sql).fetchall()
        data_res = [{
                'id': r[0],
                'x':  r[1],
                'y':  r[2],
                'z':  r[3],
                'tm': r[4],
                'ts': r[5],
                } for r in result]
        x =[r[1] for r in result]
        y =[r[2] for r in result]
        z =[r[3] for r in result]
        tm=[r[4] for r in result]
        ts=[r[5] for r in result]
        return conf, [x,y,z,tm,ts]

    def get_condition_data(self, _time):
        data = session.query(Data).filter(Data.condition_id==Condition.id).filter(Condition.time==_time).all()
        cond = session.query(Condition).filter(Condition.time==_time).first()
        if data and cond:
            x =[]
            y =[]
            z =[]
            tm =[]
            ts =[]
            for d in data:
                x.append(d.x)
                y.append(d.y)
                z.append(d.z)
                tm.append(d.tm)
                ts.append(d.ts)
            conf = {
                'ssd':cond.ssd,
                'power':cond.power,
                'raySizeX':cond.raySizeX,
                'raySizeY':cond.raySizeY,
                'type':cond.type,
                'rayType':cond.rayType,
                'deepth':cond.deepth,
                'direction':cond.direction}
            return conf,[x,y,z,tm,ts]

    def delete_condition_data(self, _time):
        def delete(data):
            for d in data:
                session.delete(d)
            session.commit()
        data = session.query(Data).filter(Data.condition_id==Condition.id).filter(Condition.time==_time).all()
        delete(data)
        cond = session.query(Condition).filter(Condition.time==_time).all()
        delete(cond)
        session.commit()
        return True

    def insert_condition_data(self, dev_id, conf):
        _time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        sql = '''insert into condition(device_id,time,rayType,type,ssd,power,direction,deepth,raySizeX,raySizeY)values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') '''%(
                dev_id,
                _time,
                str(conf['rayType']),
                str(conf['type']),
                str(conf['ssd']),
                str(conf['power']),
                str(conf['direction']),
                str(conf['deepth']),
                str(conf['raySizeX']),
                str(conf['raySizeY']),)
        res = session.execute(sql)
        session.commit()
        if res:
            sql = '''select c.id from condition c where c.time=='%s' '''%_time
            _id = session.execute(sql).fetchall()
            if _id:
                return _id[0][0]

    def insert_data(self, condition_id, data):
        for d in data:
            sql = '''insert into data(condition_id,x,y,z,tm,ts)values('%s','%s','%s','%s','%s','%s') '''%(condition_id,d[1],d[2],d[3],d[4],d[5])
            res = session.execute(sql)
        session.commit()
        return res

    def delete_data(self,condition_id):
        sql = '''delete from data where data.condition_id='%s' '''%condition_id
        session.execute(sql)
        session.commit()

    def insert_ebeam_data(self):
        data = [
        [0,0,0,194.95,0],
        [0,0,2,192,0],
        [0,0,4,193.93,0],
        [0,0,6,197.96,0],
        [0,0,8,198.17,0],
        [0,0,10,201.21,0],
        [0,0,12,202.28,0],
        [0,0,14,202.52,0],
        [0,0,16,203.62,0],
        [0,0,18,204.36,0],
        [0,0,20,204.84,0],
        [0,0,22,205.34,0],
        [0,0,24,203.77,0],
        [0,0,26,203.22,0],
        [0,0,28,198.99,0],
        [0,0,30,194.31,0],
        [0,0,32,188.19,0],
        [0,0,34,176.63,0],
        [0,0,36,164.13,0],
        [0,0,38,147.49,0],
        [0,0,40,128.68,0],
        [0,0,42,107.18,0],
        [0,0,44,84.86,0],
        [0,0,46,62.86,0],
        [0,0,48,43.12,0],
        [0,0,50,27.19,0],
        [0,0,52,15.88,0],
        [0,0,54,9.19,0],
        [0,0,56,5.9,0],
        [0,0,58,4.38,0],
        [0,0,60,3.82,0],
        [0,0,62,3.71,0],
        [0,0,64,3.58,0],
        [0,0,66,3.45,0],
        [0,0,68,0,0],
        [0,0,70,0,0],
        [0,0,72,0,0],
        [0,0,74,0,0],
        [0,0,76,0,0],
        [0,0,78,0,0],
        [0,0,80,0,0],
        ]
        cond = Condition(
                rayType   = '电子',
                type      = 'EBEAM',
                ssd       = '100cm',
                power     = '2Mev',
                direction = 'O->G',
                deepth    = '300mm',
                raySizeX  = '10cm',
                raySizeY  = '10cm',
                )
        for d in data:
            temp= Data(x=d[0],y=d[1],z=d[2],tm=d[3],ts=d[4])
            cond.datas.append(temp)
        session.add(cond)
        session.commit()

    def insert_scan_data(self):
        data = [
            [-10.0, -10.0, 100.0, -151, -154],
            [-5.0,  -10.0, 100.0, -150, -156],
            [0.0,   -10.0, 100.0, -153, -154],
            [5.0,   -10.0, 100.0, -152, -155],
            [10.0,  -10.0, 100.0, -151, -154],
            [-10.0, -5.0,  100.0, -153, -155],
            [-5.0,  -5.0,  100.0, -150, -156],
            [0.0,   -5.0,  100.0, -151, -158],
            [5.0,   -5.0,  100.0, -153, -156],
            [10.0,  -5.0,  100.0, -152, -155],
            [-10.0, 0.0,   100.0, -153, -155],
            [-5.0,  0.0,   100.0, -150, -156],
            [0.0,   0.0,   100.0, -153, -156],
            [5.0,   0.0,   100.0, -152, -158],
            [10.0,  0.0,   100.0, -151, -157],
            [-10.0, 5.0,   100.0, -153, -156],
            [-5.0,  5.0,   100.0, -154, -157],
            [0.0,   5.0,   100.0, -151, -157],
            [5.0,   5.0,   100.0, -152, -155],
            [10.0,  5.0,   100.0, -153, -155],
            [-10.0, 10.0,  100.0, -151, -155],
            [-5.0,  10.0,  100.0, -154, -157],
            [0.0,   10.0,  100.0, -152, -156],
            [5.0,   10.0,  100.0, -154, -156],
            [10.0,  10.0,  100.0, -151, -156],
        ]
        cond = Condition(
                rayType   = '电子',
                type      = 'SCAN',
                ssd       = '100cm',
                power     = '2Mev',
                direction = 'A->B',
                deepth    = '100mm',
                raySizeX  = '2cm',
                raySizeY  = '2cm',
                )
        for d in data:
            temp= Data(x=d[0],y=d[1],z=d[2],tm=d[3],ts=d[4])
            cond.datas.append(temp)
        session.add(cond)
        session.commit()

